package dblayer;

import java.sql.*;
import java.util.ArrayList;
import modellayer.Supplier;

/**
 * @author gruppe 3
 */
public class DBSupplier implements IFDBSupplier {

	private Connection con;
	
	public DBSupplier()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	/**
	 * A arrayList with all Suppliers
	 */
	public ArrayList<Supplier> getAllSuppliers() {
 	 	return miscWhere("");
	}
	
	public Supplier searchSupplierOnName(String name) {
 		String wClause = "name like % ? %";
		 try {
			 PreparedStatement pstmt = con.prepareStatement(wClause);
			 pstmt.setString(1, name);
		 } catch (SQLException sqlE) {
			 System.out.println("SQL Error");
			 System.out.println(sqlE.getMessage());
		 } catch (Exception e) {
			 e.getMessage();
		 }
 		return singleWhere(wClause);
		 
 	}
	
	public Supplier selectSingleSupplier(int supplierID) throws SQLException {
   	 String wClause = "supplierID = "+supplierID;
        return singleWhere(wClause);
    }
	
	/**
     * Insert a Supplier to the DB
     */
	public int insertSupplier(Supplier s) throws SQLException 
    {
	 int controlInt = -1;
   	 PreparedStatement pstmt = null;
   	 String insert = "insert into Supplier(name, address, country, phoneNo, email)"
                      + "values (?, ?, ?, ?, ?)";
   	 //System.out.println(insert);
   	 try {
   		 pstmt = con.prepareStatement(insert);
   		 pstmt.setString(1, s.getName());
   		 pstmt.setString(2, s.getAddress());
         pstmt.setString(3, s.getCountry());
         pstmt.setString(4, s.getPhoneNo());
         pstmt.setString(5, s.getEmail());
         controlInt = pstmt.executeUpdate();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, Supplier not inserted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
   	 	 return controlInt;
    }
	
	/**
     * Updates the Supplier db
     */
    @Override
    public int updateSupplier(Supplier s) throws SQLException {
    
     int controlInt = -1;
   	 String update = "UPDATE Supplier SET "
   	 		+ "name = ?, "
   	 		+ "address = ?, "
   	 		+ "country = ?, "
   	 		+ "phoneNo = ?, "
   	 		+ "email = ? "
   	 		+ "WHERE supplierID = ?";

   	 System.out.println(update);
   	 try {
   		 PreparedStatement pstmt = null;
   		 pstmt = con.prepareStatement(update);
   		 pstmt.setString(1, s.getName());
   		 pstmt.setString(2, s.getAddress());
         pstmt.setString(3, s.getCountry());
         pstmt.setString(4, s.getPhoneNo());
   		 pstmt.setString(5, s.getEmail());
   		 pstmt.setInt(6, s.getSupplierID());
   		 controlInt = pstmt.executeUpdate();
   	 } catch (SQLException sqlE) {
   		 System.out.println("SQL Error, Supplier not updated");
   		 System.out.println(sqlE.getMessage());
   	 } catch (Exception e) {
   		 e.getMessage();
   	 }
   	 return controlInt;
    }
    
    /**
     * Delete a Supplier from the database
     */
    public int deleteSupplier(Supplier s) throws SQLException 
    {
   	 PreparedStatement pstmt = null;
   	 int controlInt = -1;
   	 
   	 String delete = "DELETE FROM Supplier WHERE supplierID = ?";
   	// System.out.println(delete);
   	 try {
   		 pstmt = con.prepareStatement(delete);
         pstmt.setInt(1, s.getSupplierID());
         controlInt = pstmt.executeUpdate();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, Supplier not deleted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
   	 
   	 return controlInt;
    }
    
    /**
	 * If more than one Supplier is to be selected
	 * @param wClause
	 * @return ArrayList with Supplier objects
	 */
    private ArrayList<Supplier> miscWhere(String wClause)
	 {
   	 	ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
	    String query = buildQuery(wClause);
   
	    try{ // read the supplier from the database
			Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	results = stmt.executeQuery(query);
			while( results.next() ){ // loop through all suppliers and create them as objects
			    Supplier sObj = null;
			    sObj = buildSupplier(results);	
		        list.add(sObj);	
			 }//end while
			
			stmt.close();   
	     }//end try
	    catch(Exception e){
	 	 	System.out.println("Query exception - select: "+e);
		 	e.printStackTrace();
	 	 }
		 return list;
	 }
    
    /**
 	 * If only one supplier is to be selected
 	 * @param wClause
 	 * @return Supplier object
 	 */
 	 private Supplier singleWhere(String wClause)
 	 {
 	 	ResultSet results;
 	 	Supplier sObj = new Supplier();
 		        
 	 	String query = buildQuery(wClause);
 		//System.out.println(query);
 		
 		try{ 	
 			Statement stmt = con.createStatement();
 			stmt.setQueryTimeout(5);
 			results = stmt.executeQuery(query);
 			if( results.next() ){
 				sObj = buildSupplier(results);
 				stmt.close();
 			}else{ 
 				sObj = null;
 			}
 		}//end try	
 		
 		catch(Exception e){
 			System.out.println("Query exception: "+e);
 		}
 		return sObj;
 	 }
 	 
 	/**
   	 * Method to build the query
   	 * @param wClause
   	 * @return
   	 */
   	 private String buildQuery(String wClause)
   	 {
   	     String query="SELECT * FROM Supplier";
   		
   	 	if (wClause.length()>0)
   	 		query=query+" WHERE "+ wClause;
   	 		
   	 	return query;
   	 }
     
     /**
      * Builds a Supplier object from the data in the database
      * @param result
      * @return Supplier object
      * @throws SQLException
      */
     private Supplier buildSupplier(ResultSet result) throws SQLException {
    	 Supplier sObj = new Supplier();

    	 try {
    		 sObj.setSupplierID(result.getInt("supplierID"));
    		 sObj.setName(result.getString("name"));
    		 sObj.setAddress(result.getString("address"));
    		 sObj.setCountry(result.getString("country"));
    		 sObj.setPhoneNo(result.getString("phoneNo"));
    		 sObj.setEmail(result.getString("email"));
    	 } catch (Exception e) {
    		 System.out.println("error building Supplier object, "+e);
         }
         return sObj;
     }
}
